package org.afun.csms_android.utils;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by aFun on 20222/5/20.
 */

public class MyDatabaseHelper extends SQLiteOpenHelper {
    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "CSMS_android.db";

    // create user
    public static final String CREATE_USER = "create table user(" +
            "id int primary key," +
            "password text not null," +
            "account text not null," +
            "gender text not null," +
            "roleId int not null," +
            "name text not null)";

    // create student
    public static final String CREATE_STUDENT = "create table student(" +
            "stuId int primary key," +
            "account text not null," +
            "constraint fk_accountS foreign key(account) references user(account) on delete cascade)";

    // create teacher
    public static final String CREATE_TEACHER = "create table teacher(" +
            "teaId int primary key," +
            "account text not null," +
            "constraint fk_accountT foreign key(account) references user(account) on delete cascade)";

    // create manager
    // ... roleID=1 => manager

    // create course
    public static final String CREATE_COURSE = "create table course(" +
            "couId int primary key," +
            "couName text unique," +
            "credit int  not null," +
            "isAudit int not null)";

    // create stu2score
    public static final String CREATE_STU2SCORE = "create table stu2score(" +
            "ssId int primary key," +
            "stuId int not null," +
            "couId int not null," +
            "score int not null," +
            "teaId int not null," +
            "constraint fk_stuId_ss foreign key(stuId) references student(stuId) on delete cascade," +
            "constraint fk_couId_ss foreign key(couId) references course(couId) on delete cascade," +
            "constraint fk_teaId_ss foreign key(teaId) references teacher(teaId) on delete cascade)";

    // create tea2cou
    public static final String CREATE_TEA2COU = "create table tea2cou(" +
            "tcId int primary key," +
            "teaId int not null," +
            "couId int not null," +
            "constraint fk_teaId_tc foreign key(teaId) references teacher(teaId) on delete cascade," +
            "constraint fk_couId_tc foreign key(couId) references course(couId) on delete cascade)";


    private Context mContext;

    //构造方法：第一个参数Context，第二个参数数据库名，第三个参数cursor允许我们在查询数据的时候返回一个自定义的光标位置，一般传入的都是null，第四个参数表示目前库的版本号（用于对库进行升级）
    public MyDatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //调用SQLiteDatabase中的execSQL（）执行建表语句。
        db.execSQL(CREATE_USER);
        db.execSQL(CREATE_TEACHER);
        db.execSQL(CREATE_STUDENT);
        db.execSQL(CREATE_COURSE);
        db.execSQL(CREATE_STU2SCORE);
        db.execSQL(CREATE_TEA2COU);
        init(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS user");
        db.execSQL("DROP TABLE IF EXISTS teacher");
        db.execSQL("DROP TABLE IF EXISTS student");
        db.execSQL("DROP TABLE IF EXISTS course");
        db.execSQL("DROP TABLE IF EXISTS stu2score");
        db.execSQL("DROP TABLE IF EXISTS tea2cou");
        onCreate(db);
    }

    /**
     * 插入初始化数据
     */
    public void init(SQLiteDatabase db) {

        String sql[] = {
                "insert into user values(1,'123456','10001','男',1,'aFun')",
                "insert into user values(2,'123456','10002','女',1,'managerW')",
                "insert into user values(3,'123456','20001','男',2,'teacher01')",
                "insert into user values(4,'123456','20002','女',2,'teacher02')",
                "insert into user values(5,'123456','20003','男',2,'teacher03')",
                "insert into user values(6,'123456','30001','男',3,'student01')",
                "insert into user values(7,'123456','30002','女',3,'student02')",
                "insert into user values(8,'123456','30003','男',3,'student03')",
                "insert into teacher values(1,'20001')",
                "insert into teacher values(2,'20002')",
                "insert into teacher values(3,'20003')",
                "insert into student values(1,'30001')",
                "insert into student values(2,'30002')",
                "insert into student values(3,'30003')",

                // ===================================================course===================================================
                "insert into course values (1,'java',5,2)",
                "insert into course values (2,'c语言',5,2)",
                "insert into course values (3,'javascript',5,2)",
                "insert into course values (4,'c++',5,2)",
                "insert into course values (5,'软件工程',5,2)",
                "insert into course values (6,'计算机网络',5,2)",
                "insert into course values (7,'高等数学',6,2)",
                "insert into course values (8,'离散数学',6,2)",
                "insert into course values (9,'数学建模',6,2)",
                "insert into course values (10,'概率论与数理统计',6,2)",
                "insert into course values (11,'线性代数',6,1)",
                "insert into course values (12,'统计学原理',6,1)",
                "insert into course values (13,'经济学原理',6,0)",
                "insert into course values (14,'管理概论',6,0)",
                "insert into course values (15,'毛泽东思想',6,0)",
                "insert into course values (16,'大学英语',6,0)",
                "insert into course values (17,'体育与健康',6,0)",
                "insert into course values (18,'心理学理论',6,0)",
                "insert into course values (19,'数据结构',5,1)",
                "insert into course values (20,'算法分析与设计',5,1)",
                "insert into course values (21,'操作系统',5,1)",
                "insert into course values (22,'机器学习',5,1)",
                "insert into course values (23,'深度学习',5,1)",
                "insert into course values (24,'计算机视觉',5,1)",
                // ===================================================course===================================================

                // ===================================================tea2cou===================================================
                "insert into tea2cou values(1,1,1)",
                "insert into tea2cou values(2,1,3)",
                "insert into tea2cou values(3,1,4)",
                "insert into tea2cou values(4,1,7)",
                "insert into tea2cou values(5,1,8)",
                "insert into tea2cou values(6,1,18)",

                "insert into tea2cou values(7,2,1)",
                "insert into tea2cou values(8,2,2)",
                "insert into tea2cou values(9,2,3)",
                "insert into tea2cou values(10,2,4)",
                "insert into tea2cou values(11,2,16)",
                "insert into tea2cou values(12,2,17)",

                "insert into tea2cou values(13,3,5)",
                "insert into tea2cou values(14,3,10)",
                "insert into tea2cou values(15,3,12)",
                "insert into tea2cou values(16,3,15)",
                "insert into tea2cou values(17,3,13)",
                "insert into tea2cou values(18,3,17)",
                "insert into tea2cou values(19,3,6)",

                "insert into tea2cou values(20,2,7)",
                "insert into tea2cou values(21,2,8)",


                // ===================================================tea2cou===================================================

                // ===================================================stu2score===================================================
                "insert into stu2score values(1,1,1,99,1)",
                "insert into stu2score values(2,1,2,59,2)",
                "insert into stu2score values(3,1,3,60,2)",
                "insert into stu2score values(4,1,4,78,1)",
                "insert into stu2score values(5,1,5,76,3)",
                "insert into stu2score values(6,1,6,43,1)",

                "insert into stu2score values(7,2,1,11,2)",
                "insert into stu2score values(8,2,2,66,2)",
                "insert into stu2score values(9,2,3,22,1)",
                "insert into stu2score values(10,2,4,33,1)",
                "insert into stu2score values(11,2,5,44,1)",
                "insert into stu2score values(12,2,6,55,1)",

                "insert into stu2score values(13,3,1,99,1)",
                "insert into stu2score values(14,3,2,77,2)",
                "insert into stu2score values(15,3,3,88,1)",
                "insert into stu2score values(16,3,4,66,1)",
                "insert into stu2score values(17,3,5,55,3)",
                "insert into stu2score values(18,3,6,100,1)",
                // ===================================================stu2score===================================================

        };
        for (int i = 0; i < sql.length; i++) {
            db.execSQL(sql[i]);
        }
    }
}
